/*DONE* Table [dbo].[<CompanyName>$Sales Type$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 29.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Sales Type$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Sales Type$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Code] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](30) NOT NULL,
	[Report ID] [int] NOT NULL,
	[Request Salesperson] [tinyint] NOT NULL,
	[Request Deposit (_)] [int] NOT NULL,
	[Request Charge Account] [tinyint] NOT NULL,
	[Purchasing Code] [nvarchar](10) NOT NULL,
	[Default Order Limit] [decimal](38, 20) NOT NULL,
	[Limit Setting] [int] NOT NULL,
	[Request Confirmation] [tinyint] NOT NULL,
	[Request Description] [int] NOT NULL,
	[New Global Dimension 2] [nvarchar](20) NOT NULL,
	[Suspend Printing] [int] NOT NULL,
	[Suspend Type] [int] NOT NULL,
	[Pre-Posting Report ID] [int] NOT NULL,
	[Prepayment Account No_] [nvarchar](20) NOT NULL,
	[Minimum Deposit] [decimal](38, 20) NOT NULL,
	[Print Item Lines on POS Slip] [tinyint] NOT NULL,
	[Voided Prepayment Account No_] [nvarchar](20) NOT NULL,
	[Days Open Trans_ Exist] [int] NOT NULL,
	[VAT Bus_ Posting Group] [nvarchar](10) NOT NULL,
	[Price Group] [nvarchar](10) NOT NULL,
	[Trans_ Delete Reminder] [int] NOT NULL,
	[Location Code] [nvarchar](10) NOT NULL,
	[Payment is Prepayment] [tinyint] NOT NULL,
	[Calc_ Price from VAT Price] [tinyint] NOT NULL,
	[Descr_ Request Caption] [nvarchar](30) NOT NULL,
	[New Global Dimension 1] [nvarchar](20) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Global Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Global Dimension 1 Code] [nvarchar](20) NOT NULL,
 CONSTRAINT ['+@Company+'$Sales Type$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Sales Type$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END